//
//  ViewController.m
//  DataBaseTest
//
//  Created by 赵小燕 on 16/12/11.
//  Copyright © 2016年 xiaoyanzhao. All rights reserved.
//

#import "ViewController.h"
#import "sqlite3.h"

static sqlite3 *dataBase;
static sqlite3 *dataBaseW;
static char dbPath[200];
static dispatch_queue_t queue;
static dispatch_queue_t queueW;

static int lastReadCount = 0;
static int readCount = 0;
static int lastWriteCount = 0;
static int writeCount = 0;


@interface ViewController () {

}


@end

@implementation ViewController

- (void)count {
    int lastRead = lastReadCount;
    int lastWrite = lastWriteCount;
    lastReadCount = readCount;
    lastWriteCount = writeCount;
    NSLog(@"%d, %d", lastReadCount - lastRead, lastWriteCount - lastWrite);
}

- (void)viewDidLoad {
    [super viewDidLoad];
    
//    if (openDb() != NULL) {
//        if (sqlite3_exec(dataBase, "DELETE FROM TEST", NULL, NULL, NULL) != SQLITE_OK) {
//            NSLog(@"delete failed");
//        }
//    }

    sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
    
    NSLog(@"%d", sqlite3_threadsafe());
    NSLog(@"%s", sqlite3_libversion());
    
    NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *directory = [path objectAtIndex:0];
    strcpy(dbPath, [[directory stringByAppendingPathComponent:@"data.db"] UTF8String]);
    NSLog(@"111111 %@", directory);
    
    dataBase = openDb(dataBase);
    dataBaseW = openDb(dataBaseW);
    
    char *errorMsg;
    NSString *str = @"CREATE TABLE IF NOT EXISTS TEST (ID INTEGER PRIMARY KEY, VALUE INTEGER);";
    if (sqlite3_exec(dataBase, [str UTF8String], nil, nil, &errorMsg) != SQLITE_OK) {
        NSLog(@"Failed to create Table : %s", errorMsg);
    }
    
    if (dataBase != NULL) {
        if (sqlite3_exec(dataBase, "DELETE FROM TEST", NULL, NULL, NULL) != SQLITE_OK) {
            NSLog(@"delete failed");
        }
    }
    
    queue = dispatch_queue_create("queue.db", NULL);
    queueW = dispatch_queue_create("queueW.db", NULL);
    [NSTimer scheduledTimerWithTimeInterval:1.0 target:self selector:@selector(count) userInfo:nil repeats:YES];
    
    insertData();
//    readData();
    writeData();

}

static void insertData() {
    char *errorMsg;

    if (sqlite3_exec(dataBase, "BEGIN TRANSACTION", NULL, NULL, &errorMsg) != SQLITE_OK) {
        NSLog(@"failed to begin transaction : %s", errorMsg);
    }
    
    static const NSString *sql = @"INSERT INTO TEST VALUES (NULL, ?);";
    sqlite3_stmt *stmt;
    if (sqlite3_prepare_v2(dataBase, [sql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
        for (int i = 0; i < 100; ++i) {
            sqlite3_bind_int(stmt, 1, arc4random());
            if (sqlite3_step(stmt) != SQLITE_DONE) {
                --i;
                NSLog(@"Error inserting table : %s", sqlite3_errmsg(dataBase));
            }
            sqlite3_reset(stmt);
        }
        sqlite3_finalize(stmt);
    }
    
    if (sqlite3_exec(dataBase, "COMMIT TRANSACTION", NULL, NULL, &errorMsg) != SQLITE_OK) {
        NSLog(@"failed to commit transaction: %s", errorMsg);
    }
    
    static const char *query = "SELECT COUNT(*) FROM TEST;";
    if (sqlite3_prepare_v2(dataBase, query, -1, &stmt, NULL) == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            NSLog(@"Table size: %d", sqlite3_column_int(stmt, 0));
        } else {
            NSLog(@"failed to read Table : %s", sqlite3_errmsg(dataBase));
        }
        sqlite3_finalize(stmt);
    }
}

static void readData() {
    static const char *query = "SELECT VALUE FROM TEST WHERE VALUE < ? ORDER BY VALUE DESC LIMIT 1;";
    
    static void (^ readBlock)() = ^(){
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(dataBase, query, -1, &stmt, NULL) == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, arc4random());
            int returnCode = sqlite3_step(stmt);
            if (returnCode == SQLITE_ROW || returnCode == SQLITE_DONE) {
                ++readCount;
            }
            sqlite3_finalize(stmt);
        } else {
            NSLog(@"failed to prepare statement : %s", sqlite3_errmsg(dataBase));
        }
        dispatch_async(queue, readBlock);
    };
    dispatch_async(queue, readBlock);
}

static void writeData() {
    static const char *update = "UPDATE test SET value = ? WHERE id = ?;";
    
    static void (^ writeBlock)() = ^(){
        sqlite3_stmt *stmt;
        if (sqlite3_prepare_v2(dataBaseW, update, -1, &stmt, NULL) == SQLITE_OK) {
            sqlite3_bind_int(stmt, 1, arc4random());
            sqlite3_bind_int(stmt, 2, arc4random() % 100 + 1);
            if (sqlite3_step(stmt) == SQLITE_DONE) {
                ++writeCount;
            }
            sqlite3_finalize(stmt);
        } else {
            NSLog(@"failed to prepare statement : %s", sqlite3_errmsg(dataBaseW));
        }
        dispatch_async(queueW, writeBlock);
    };
    dispatch_async(queueW, writeBlock);
}

static sqlite3 *openDb(sqlite3* dataBase) {
    if (sqlite3_open(dbPath, &dataBase) != SQLITE_OK) {
        sqlite3_close(dataBase);
        NSLog(@"failed to open database");
    }
    return dataBase;
}

//- (void)insertTable:(UIButton *)button {
//    NSLog(@"start to create DataBase");
//    if (sqlite3_open([[self dataFilePath] UTF8String], &dataBase) != SQLITE_OK) {
//        sqlite3_close(dataBase);
//        NSLog(@"表打开失败");
//    }
//    char *insert = "INSERT INTO PERSON_INFO('NAME', 'AGE', 'SEX', 'WEIGHT') VALUES(?, ?, ?, ?)";
//    
//    sqlite3_stmt *stmt;
//    
//    if (sqlite3_prepare(dataBase, insert, -1, &stmt, nil) == SQLITE_OK) {
//        sqlite3_bind_text(stmt, 1, [_textField.text UTF8String], -1, NULL);
//        sqlite3_bind_int(stmt, 2, [_textField1.text intValue]);
//        sqlite3_bind_text(stmt, 3, [_textField2.text UTF8String], -1, NULL);
//        sqlite3_bind_int(stmt, 4, [_textField3.text intValue]);
//    }
//    if (sqlite3_step(stmt) != SQLITE_DONE) {
//        NSLog(@"数据更新失败");
//        
//        sqlite3_finalize(stmt);
//        sqlite3_close(dataBase);
//    }
//}


- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


@end
